package com.example.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import com.example.entity.User;

public class UserDAO {
	
	private static Connection getConnection() throws ClassNotFoundException,
			SQLException {
		Class.forName("com.ibm.db2.jcc.DB2Driver");
		String url = "jdbc:db2://vampir.cosy.sbg.ac.at:50001/DBVWS12";
		return DriverManager.getConnection(url, "cline", "h!jsfu382!2");
	}

	private static void closeConnection(Connection connection)
			throws SQLException {
		if (connection != null) {
			connection.close();
		}
	}

	public static List<User> getUsers() throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select * from personal_data order by pd_sirname asc fetch first 10 rows only";
		try {
			connection = getConnection();
			statement = connection.prepareStatement(statementString);
			ResultSet resultSet = statement.executeQuery();

			List<User> result = null;
			if (!resultSet.isClosed()) {
				result = new LinkedList<User>();
				while (resultSet.next()) {
					User user = new User();
					user.setForename(resultSet.getString("PD_FORENAME"));
					user.setSurname(resultSet.getString("PD_SIRNAME"));				
					result.add(user);
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			closeConnection(connection);
		}
	}
}
